package com.yuanren.em.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yuanren.em.entity.PointsExchangeProduct;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * 积分兑换商品Mapper接口
 */
public interface PointsExchangeProductMapper extends BaseMapper<PointsExchangeProduct> {

    /**
     * 分页查询积分兑换商品（前台展示）
     */
    @Select("SELECT * FROM t_points_exchange_product " +
            "WHERE is_active = true " +
            "AND (#{category} IS NULL OR category = #{category}) " +
            "AND (#{productType} IS NULL OR product_type = #{productType}) " +
            "AND (#{minPoints} IS NULL OR points_required >= #{minPoints}) " +
            "AND (#{maxPoints} IS NULL OR points_required <= #{maxPoints}) " +
            "ORDER BY sort_order ASC, points_required ASC")
    IPage<PointsExchangeProduct> selectActiveProductsPage(Page<PointsExchangeProduct> page,
                                                          @Param("category") String category,
                                                          @Param("productType") String productType,
                                                          @Param("minPoints") Integer minPoints,
                                                          @Param("maxPoints") Integer maxPoints);

    /**
     * 分页查询积分兑换商品（后台管理）
     */
    @Select("SELECT * FROM t_points_exchange_product " +
            "WHERE (#{category} IS NULL OR category = #{category}) " +
            "AND (#{productType} IS NULL OR product_type = #{productType}) " +
            "AND (#{isActive} IS NULL OR is_active = #{isActive}) " +
            "AND (#{name} IS NULL OR name LIKE CONCAT('%', #{name}, '%')) " +
            "ORDER BY sort_order ASC, created_at DESC")
    IPage<PointsExchangeProduct> selectProductsPageForAdmin(Page<PointsExchangeProduct> page,
                                                            @Param("category") String category,
                                                            @Param("productType") String productType,
                                                            @Param("isActive") Boolean isActive,
                                                            @Param("name") String name);

    /**
     * 获取热门兑换商品
     */
    @Select("SELECT p.*, COALESCE(r.exchange_count, 0) as exchange_count " +
            "FROM t_points_exchange_product p " +
            "LEFT JOIN (" +
            "    SELECT product_id, COUNT(*) as exchange_count " +
            "    FROM t_points_exchange_record " +
            "    WHERE status = 'COMPLETED' " +
            "    AND DATE(exchange_date) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "    GROUP BY product_id" +
            ") r ON p.id = r.product_id " +
            "WHERE p.is_active = true " +
            "ORDER BY exchange_count DESC, p.sort_order ASC " +
            "LIMIT #{limit}")
    List<PointsExchangeProduct> selectHotProducts(@Param("limit") Integer limit);

    /**
     * 减少商品库存
     */
    @Update("UPDATE t_points_exchange_product SET stock_quantity = stock_quantity - #{quantity} " +
            "WHERE id = #{productId} AND stock_quantity >= #{quantity}")
    int reduceStock(@Param("productId") Long productId, @Param("quantity") Integer quantity);

    /**
     * 增加商品库存
     */
    @Update("UPDATE t_points_exchange_product SET stock_quantity = stock_quantity + #{quantity} " +
            "WHERE id = #{productId}")
    int increaseStock(@Param("productId") Long productId, @Param("quantity") Integer quantity);

    /**
     * 统计商品兑换情况
     */
    @Select("SELECT " +
            "COUNT(*) as total_products, " +
            "SUM(CASE WHEN is_active = true THEN 1 ELSE 0 END) as active_products, " +
            "SUM(CASE WHEN stock_quantity > 0 THEN 1 ELSE 0 END) as in_stock_products, " +
            "AVG(points_required) as avg_points_required " +
            "FROM t_points_exchange_product")
    Map<String, Object> selectProductStats();

    /**
     * 根据分类统计商品数量
     */
    @Select("SELECT category, COUNT(*) as count, " +
            "SUM(CASE WHEN is_active = true THEN 1 ELSE 0 END) as active_count " +
            "FROM t_points_exchange_product " +
            "GROUP BY category")
    List<Map<String, Object>> selectProductCountByCategory();

    /**
     * 检查商品库存
     */
    @Select("SELECT stock_quantity FROM t_points_exchange_product WHERE id = #{productId}")
    Integer selectStockQuantity(@Param("productId") Long productId);
} 